
*******************************************************
*** Summary Statistics 
*******************************************************

clear all
set more off

cd "$FOLDER"
use czech_pinter_cb_data, clear

******************************************************************
*** Table 1
******************************************************************

*** Average Daily Volume (in £ millions)
* quantity in GBP pounds → divide by 1e6

cap drop aux*
bys tradedate: gen aux = _n==1

gen aux_rat = 0
replace aux_rat = 1 if ig == 1
replace aux_rat = 2 if hy == 1

bys tradedate: egen aux_quant = total(quantity)
gen aux_quant_mn = aux_quant / 1e6   // £ millions

bys tradedate aux_rat: egen aux_quant_rat = total(quantity)
gen aux_quant_rat_mn = aux_quant_rat / 1e6   // £ millions

bys tradedate aux_rat: gen aux_day_rat = _n==1

tabstat aux_quant_mn if aux == 1

* Investment Grade
tabstat aux_quant_rat_mn if aux_day_rat == 1 & ig == 1

* High Yield
tabstat aux_quant_rat_mn if aux_day_rat == 1 & hy == 1

* Not Rated
tabstat aux_quant_rat_mn if aux_day_rat == 1 & rating_consens == 23


*** Average Number of Transactions (per day)
bys tradedate: egen aux_quant2 = count(quantity)
bys tradedate aux_rat: egen aux_quant_rat2 = count(quantity)

tabstat aux_quant2 if aux == 1

* Investment Grade
tabstat aux_quant_rat2 if aux_day_rat == 1 & ig == 1

* High Yield
tabstat aux_quant_rat2 if aux_day_rat == 1 & hy == 1

* Not Rated
tabstat aux_quant_rat2 if aux_day_rat == 1 & ig == 0 & hy == 0


** Number of Bonds
bys instrumentid: gen aux_ins = _n==1
tabstat aux_ins, s(sum)

** Number of Issuers
bys issuer: gen aux_iss = _n==1
tabstat aux_iss, s(sum)

** Median time-to-maturity
tabstat time_to_maturity, s(median)

** Industry
tabstat aux_iss, s(sum) by(industry_sector)



*** Number of Investors (unique IDs in `call`)
use Perf2020jan.dta, clear

* Drop any old helper variable
cap drop aux_call

* Tag unique investor IDs
bys call: gen aux_call = _n==1

* Total unique investors
tabstat aux_call, s(sum)

* Sophisticated investors only (active == 1)
tabstat aux_call if active == 1, s(sum)


******************************************************************
*** Table 2
******************************************************************

******************************************************************
* Connections Analysis
******************************************************************

cd "$FOLDER"

use aggregate_day_all, clear

tabstat day_OR1_ful day_OR1_bip transactionDaily clientVolumeDaily if active==1, stats(mean median p10 p90 sd count) columns(statistics)
   


******************************************************************
* Market Structure Analysis: Dealer Concentration and HHI
******************************************************************

cd "$FOLDER"

* Load transactions
use czech_pinter_cb_data.dta, clear

* Duplicate rows for buyer/seller perspective
expand 2, gen(flagDUP)

* Client ID
cap drop call
gen long call = buyer  if flagDUP == 0
replace  call = seller if flagDUP == 1

* Client Type
cap drop callTy
gen callTy = buyer_type  if flagDUP == 0
replace callTy = seller_type if flagDUP == 1

* Buy-sell indicator
cap drop bs_clients
gen bs_clients = buysellnumeric       if flagDUP == 0
replace bs_clients = buysellnumeric*-1 if flagDUP == 1

* Counterparty ID
cap drop coun
gen long coun = buyer  if flagDUP == 1
replace  coun = seller if flagDUP == 0

* Counterparty Type
cap drop counTy
gen counTy = buyer_type  if flagDUP == 1
replace counTy = seller_type if flagDUP == 0

******************************************************************
* Dealer counts per instrument-month and instrument-day
******************************************************************
* Investor/instrument/month flag
bys instrumentid call monthyear: gen aux_cp = _n==1
bys instrumentid monthyear: egen sum_dealers = total(aux_cp) if dealer==1

gsort instrumentid monthyear -sum_dealers
bys instrumentid monthyear: gen auxm = _n==1

* Investor/instrument/day flag
bys instrumentid call tradedate: gen aux_cp2 = _n==1
bys instrumentid tradedate: egen sum_dealers2 = total(aux_cp2) if dealer==1

gsort instrumentid tradedate -sum_dealers2
bys instrumentid tradedate: gen auxd = _n==1

******************************************************************
* Herfindahl-Hirschman Index (HHI) for dealer concentration
******************************************************************
* Flag first dealer observation each month
bys instrumentid monthyear call: gen aux_cp3 = _n==1  if dealer==1

* Volume per investor/instrument/month
bys monthyear instrumentid call: egen vol_inv = total(quantity)

* Total dealer volume per instrument/month
bys monthyear instrumentid: egen aux_volume2 = total(quantity) if dealer==1

* Market share and squared share
gen m_sharedlr    = vol_inv / aux_volume2
gen m_sharedlr_sq = m_sharedlr^2

* Compute HHI
bys monthyear instrumentid: egen hhi_dlr = total(m_sharedlr_sq) if aux_cp3==1

* Flag for HHI summary
gsort instrumentid monthyear -hhi_dlr
bys instrumentid monthyear: gen auxhhi = _n==1

******************************************************************
* Market share of top 1, 2, 3 dealers
******************************************************************
gsort monthyear instrumentid -m_sharedlr -aux_cp3
by monthyear instrumentid: gen cum_dealers = sum(aux_cp3) 

bys monthyear instrumentid: egen top3_dealers = total(m_sharedlr) if cum_dealers<=3 & aux_cp3==1

bys monthyear instrumentid: egen top2_dealers = total(m_sharedlr) if cum_dealers<=2 & aux_cp3==1

bys monthyear instrumentid: egen top1_dealers = total(m_sharedlr) if cum_dealers==1 & aux_cp3==1


******************************************************************
* Summary statistics 
******************************************************************

tabstat top1_dealers top2_dealers top3_dealers if cum_dealers==1, stats(mean median p10 p90 sd) columns(statistics)

tabstat sum_dealers2 if auxd==1, stats(mean median p10 p90 sd) columns(statistics)

tabstat sum_dealers hhi_dlr if auxm==1, stats(mean median p10 p90 sd) columns(statistics)

tabstat hhi_dlr if auxhhi==1, stats(mean median p10 p90 sd) columns(statistics)
